CREATE TABLE subject (
    id serial PRIMARY KEY,
    name varchar(100),
    sysname varchar(100)
);

INSERT INTO subject (name, sysname) VALUES ('Физика', 'PHYSIC'), ('Математика', 'MATH');

CREATE TABLE source (
    id serial PRIMARY KEY,
    name varchar(300),
    subject_id integer REFERENCES subject
);

CREATE TABLE level (
    id serial PRIMARY KEY,
    name varchar(100),
    subject_id integer REFERENCES subject
);

INSERT INTO level (name, subject_id) VALUES
('Легкий', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Средний', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Достаточный', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Высокий', (SELECT id FROM subject WHERE sysname = 'PHYSIC'));

CREATE TABLE theme (
    id serial PRIMARY KEY,
    name varchar(100),
    subject_id integer REFERENCES subject
);

INSERT INTO theme (name, subject_id) VALUES
('Кинематика', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Динамика', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Закон сохранения', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Механические колебания и волны', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('МКТ', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Термодинамика', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('С часть по МКТ и ТД', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Электростатика', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Постоянный ток', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Магнитное поле', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Электромагнитная индукция', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Электромагнитные колебания и волны', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Оптика', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Квантовая', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Атомная', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('СТО', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Астрономия', (SELECT id FROM subject WHERE sysname = 'PHYSIC'));

CREATE TABLE number_in_kim (
    id serial PRIMARY KEY,
    name varchar(100),
    subject_id integer REFERENCES subject
);

INSERT INTO number_in_kim (name, subject_id) VALUES
('1', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('2', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('3', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('4', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('5', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('6', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('7', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('8', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('9', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('10', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('11', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('12', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('13', (SELECT id FROM subject WHERE sysname = 'PHYSIC'));

INSERT INTO source (name, subject_id) VALUES
('Сборник задач по физике', (SELECT id FROM subject WHERE sysname = 'PHYSIC')),
('Сайт', (SELECT id FROM subject WHERE sysname = 'PHYSIC'));

CREATE TABLE physic_task (
    id serial PRIMARY KEY,
    "text" text,
    tags varchar[],
    photo_ids varchar[],
    solution_photo_ids varchar[],
    level_id integer REFERENCES level,
    theme_id integer REFERENCES theme,
    source_id integer REFERENCES source,
    number_in_kim_id integer REFERENCES number_in_kim
);